Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
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 ?
Is the field called Annees or ANNEES (or even different)? Qlik is case sensitive when using field names.
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) ?
Maybe with this kind of expression ?
Sum([{SetExpression}] [DISTINCT] [TOTAL [<fld {,fld}>]] expr])
but i don't understand where write or not { , [ ... ...
regards
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 ?
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.
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
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)