10 Replies Latest reply: Oct 21, 2017 9:29 AM by Philippe BONNIN

# PARTICULAR SYNTAX SUM TOTAL {1}

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 ?

Philippe

• ###### Re: PARTICULAR SYNTAX SUM TOTAL {1}

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)

• ###### Re: PARTICULAR SYNTAX SUM TOTAL {1}

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 :-(

• ###### Re: PARTICULAR SYNTAX SUM TOTAL {1}

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

• ###### Re: PARTICULAR SYNTAX SUM TOTAL {1}

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) ?

• ###### Re: PARTICULAR SYNTAX SUM TOTAL {1}

Maybe with this kind of expression ?

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

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

regards

• ###### Re: PARTICULAR SYNTAX SUM TOTAL {1}

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 ?

• ###### Re: PARTICULAR SYNTAX SUM TOTAL {1}

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.

• ###### Re: PARTICULAR SYNTAX SUM TOTAL {1}

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?

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

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

Regards

Philippe

• ###### Re: PARTICULAR SYNTAX SUM TOTAL {1}

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)

• ###### Re: PARTICULAR SYNTAX SUM TOTAL {1}

hI BECKI

as master Stephan wrote :

the 1 is to ignore all selections in your model

then Annees = \$::Annees allows only years selection

or

with p(), select only year you want to be considered

Regards

Philippe