PARTICULAR SYNTAX SUM TOTAL {1}

[philgood34]

Hi

i have a table with sectors (named "secteurs") and sales (named "VMS_CATTC")

In the table below you'll see the total sales 2016 for all sectors

As you see the TOTAL 2016 =35444655 , and i trained with different syntaxes using TOTAL or not ...

Now my issue:

several sectors don't produce sales as administration, so if i select administration obviously i have sales = 0

Ok .

for reason of calculation of ratios (in others tables) i need an expression which give the result 35444655 EVEN IF  I SELECT only 1 sector producing or not sales ...

In other words, an expression which give TOTAL SALES OF 2016 regardless of the selected sector ( but with the selection of the year being operational) ... I undertstood that {1}TOTAL expression is not appropriate because of this last point

i thought that with my Sum({<Flag = {'CY'}>}TOTAL VMS_CATTC) it would be correct , as you see above, it is not ...

is there a trick using the syntax ?

thank's in advance

Philippe

[swuehl · Accepted solution]

Hard to tell the correct syntax without knowing your data model, but in general, you need to use set analysis to ignore selections or modify field selections and there are two basic approaches for your issue:

1) Use set identifier 1 to ignore all selections and then modifiy the field selections where you need to consider user selections

Sum({1<Flag = {'CY'}, Annees = $::Annees) >}TOTAL VMS_CATTC)

or

Sum({1<Flag = {'CY'}, Annees = p() >}TOTAL VMS_CATTC)

First expression will use selected Annees, second will use possible Annees values.

2) Use set identifier $ to use the current selection state, but ignore specific field selections:

Sum({<Flag = {'CY'}, SECTEURS= >} TOTAL VMS_CATTC)

[philgood34]

Hi Stefan

great thank's to your answer (like always give reponse to the issue but also let us learn ...)

I think that  these expression is the good one for my issue

Sum({1<Flag = {'CY'}, Annees = $::Annees >}TOTAL VMS_CATTC)

but it seems that it does not work 😞

anything i made wrong ?

[swuehl]

Is the field called Annees or ANNEES (or even different)? Qlik is case sensitive when using field names.

[philgood34]

Ok Stefan , your are right . my field is named ‘Year ‘so

Sum({1<Flag = {'CY'}, Year= $::Year>}TOTAL VMS_CATTC) is the right expression

Unfortunatly, my data model does’nt allow me to use it :

In fact, i created a nomenclature table wich is a short list of the global nomenclature that exists in the datas tables imported from ERP .. my goal is to symplify the vizualations for users… using  the short list

So when i use the expression, the total calculated is apply on the global nomenclature of course… gasp !

Is there a another possibility based on your second solution but in an opposite way  :

Sum({<Flag = {'CY'}, SECTEURS= sector 1,sector2… >} TOTAL VMS_CATTC) ?

Sectors 1, 2… corresponding to my short list

Ps : in these expression i don’t see the $ you talk about in your point 2) ?

[philgood34]

Maybe with this kind of expression ?

Sum([{SetExpression}] [DISTINCT] [TOTAL [<fld {,fld}>]] expr])

but i don't understand where write or not { , [ ... ...

regards

[philgood34]

I think i found the solution for my particular data model

let me suggest !

i have in my nomenclature table (short list) a field named   MAG. Hors serv.&Presse wich group all the sectors in that short list (excluding unnecessary sectors in the data TABLE)

so i use your first suggestion :

Sum({1<Flag = {'CY'}, Year = $::Year,ACTIVITE ={'MAG. Hors serv.&Presse'} >}TOTAL VMS_CATTC)

it seems working ..

what do you think about ?

[swuehl]

Sorry, I am not sure how your data model looks like and how your nomenclature table works.

But latter expression will only be responsive to selections in Year field, so if that's what you want and if the numbers are correct, you should be fine.

[philgood34]

Hi Stephan

Yes it is fine ! be thanked for it ...

and I keep your 3 expressions preciously!

can I suggest you to take a look at one of my previous unanswered messages?

INTEGRATE NEW TABLE IN LOADING SCRIPT

it is about the connection between 3 tables with dates in months and years for 2 tables and in years for the last one.

I have to connect them in an already existing loading script ...

if my explanations are sufficient, I think it should not be difficult for you

Thank’s in advance

Regards

Philippe

[becki_kain]

what does the p do in this line:

Sum({1<Flag = {'CY'}, Annees = p() >}TOTAL VMS_CATTC)

and what does Annees = $::Annees (that syntax mean)?  thanks

Sum({1<Flag = {'CY'}, Annees = $::Annees) >}TOTAL VMS_CATTC)