Table result incorrect

[gerald_lebret]

Hello,

I've created a table where I compare revenue between current year and last year by item group in Qlik Sense. The result in Qlik Sense is correct.

I then created a report in Nprinting where I want to add this table.

Thus, I created a level for my item group dimension and I nested another level with this table.

My problem is that whenever an Item group does not have any revenue for year N, the whole line is not displayed in my printed report, even if there is some revenue for year N-1

Can anyone help, please?

PS: I did not choose to delete null value

[Ruggero_Piccoli]

Hi,

You will have a row in the generated report for each distinct value of the first field, or table, you placed inside Levels. If in Qlik Sense tat Field or Table has not a row for yearN you will not have the related row in the report. The whole band is deleted because it is hierarchically under the previous one.  If the main field of the level miss a row all the content under it will be deleted.

You shared only a small part of the template so I cannot understand how you created the hierarchy of the bands. So I also suggest you check that it is correct. If not, the typical result is a table with only one line. 

Best Regards,

Ruggero

[Lech_Miszkiewicz]

Hi, 

This type of issues are typical when you have this year and last year handled by expressions and set analysis without actually using a reference calendar with this year and last year flags. That (using just set analysis to override Year selection) leads to scenarios where looking just on 1 year at the time you may have Item group completely excluded from selection. 

It is not really NPrinting problem and I will put that problem into Qlik Sense design issue bucket. In reality for that year you select you may not have "Item Group" available in field selection (when you preview Item groups in filter pane or selections interface) and only visible in the table because of set analysis which will not work in NPrinting.

Those scenarios need to be understood and designed in Qlik Sense properly in first place.

cheers

[gerald_lebret]

HI,

Your answer is quite interesting.

My QS app contains one table for the revenue and each transaction has a date.

I then have a calendar table.

I will then specify in my set analysis formula the year I wan to see.

Furthermore, I use a variable to calculate year N and year N-1.

Can I provide you with more information?

Thanks.

[Lech_Miszkiewicz]

Hi, exactly: 

I will then specify in my set analysis formula the year I wan to see.

Furthermore, I use a variable to calculate year N and year N-1.

So looking at your calendar table when you have selected year 2023 you are actually excluding from selections records for 2022 in your transaction (when looking just at what is selected and what is excluded in current selection state). Am I right?

The way I would handle it is as follows:

Have a Calendar table which builds possible references. When I select Year 2023 (green) I am not excluding records from my current selection for Last Year as my Calendar table has references to Current Year and Last Year and I use Flag to filter which Year I want to show as current and which as last year. 

So the difference is in my option I have both years "possible" when I select single year and I use set analysis to further limit visible records. You on the other hand do opposite - you select single year and the-facto exclude last year from selection and then use set analysis to override that exclusion which from NPrinting point of view is completely different scenario.

does that make sense?

cheers

[gerald_lebret]

Hi,

Explained like this, your answer makes sense.

However, since I have measures with revenue and quantities for year N and revenue and quantities for year N-1, I am not looking at one year at a time as you mentioned in your previous answer and therefore, I do not understand why it does not work.

Anyway, if I go for your solution, how would you build the calendar table? I have pretty much one row per day for the last 15 years (no I don't need all the dates...).

Thanks

[Lech_Miszkiewicz]

Well - and here it is tricky part - I would need to see your app and NPrinting template (all details to understand it better). My suggestions were based on the fact that you apply some filter which changes selection state (for example you apply selection on Max year.

You say:

However, since I have measures with revenue and quantities for year N and revenue and quantities for year N-1, I am not looking at one year at a time as you mentioned in your previous answer and therefore, I do not understand why it does not work.

I understand that, but question is:

• how do you define what is year N and Year N-1 ( i know you do it in variables, but the actual method also may be important).
  • Is it something like Year(max(Date)) and Year(Max(Date))-1 ?
  • then if that is the case what are the NPrinting filters (if any) applied
    • as soon as you apply filter like MAX(Year) your Qlik Sense table will work, but you will exclude Item Groups which dont exist in that year from your first level which is based on Item Group field, right?

your other question was: 

Anyway, if I go for your solution, how would you build the calendar table?

you need duplicate calendar rows as many times as many years you want to show. If it is only last year then you duplicate number of rows and offset Date key field by 1 year and create flags - I am sure you came across this solution many times as it is very common approach to calendar giving you quite a freedom and ease in expressions syntax.

I understand that it will change the way whole app needs to be built, so before you do anything please ensure that this solution would work for you. 

cheers

[gerald_lebret]

Hello,

When I make my selection, I select my outlet, year 2023 from my calendar  and month 1 to 4

Then my formulas use set analysis and refers to my yearN and YearN-1 variables which are calculated as follows:

For Year N:"=max(Annee_fiscale)";

For Year N-1"=if(GetSelectedCount(Annee_fiscale) > 1, max(Annee_fiscale, 2), vAnneeN - 1)";

Thus, for instance, to get my revenue for year N, I use the followintg formula:

Sum({$ < [DOC.Type_document_groupe_code]={$(vOPFactures)},
Annee_fiscale ={$(vAnneeN)},
Mois={"=Mois<=max(Mois)"},
[LIG.Type_de_ligne]={$(vTypeDeLigne)}
>} [LIG.Montant_HT_devise_succursale]
)
-
Sum({$ < [DOC.Type_document_groupe_code]={$(vOPNotesCredit)},
Annee_fiscale ={$(vAnneeN)},
Mois={"=Mois<=max(Mois)"},
[LIG.Type_de_ligne]={$(vTypeDeLigne)}
>} [LIG.Montant_HT_devise_succursale]
)
-
Sum({$ < Annee_fiscale ={$(vAnneeN)},
Mois={"=Mois<=max(Mois)"},
[LIG.Type_de_ligne]={$(vTypeDeLigne)},
[LIG.Cle_article]={'60002','60008'}
>} [LIG.Montant_HT_devise_succursale]
)

Thanks for your assistance.

Best regards

[gerald_lebret · Accepted solution]

Hi @Lech_Miszkiewicz , @Ruggero_Piccoli ,

I did end up finding the solution.

Indeed, my table intends to compares quantities and revenues per item group between Year N and Year N-1.

As explained, I created my first level with item group and I nested my table as a sublevel into that first level.

I did try many diferent options without finding the solution.

Then, I decided to try to create my table as a level with having the item group as an upper level and it worked like a charmed.

Thanks again for your help.

Best regards.

Gerald