sum according to dimension value

[Captain_Jack]

Hello ,

I am new to Qlik and Qlik sense ,  I am stucking in sum the value based on another value in the dimension(category)

hope someone could help me out here. thanks!

I have a table on the left , and I want to sum the value where year_week = snapshot_week per snapshot,

which means I would like to get on snapshot_week 202112 = 100 ,  snapshot 202113 =101, snapshot 202114 = 200.

and also sum value where year_week < snapshot_week per snapshot.

meaning Snapshot 202112 = 200 ,  snapshot 202113 =303, snapshot 202114 = 800.

and I used   sum({<year_week={"<(=Min(Snapshot_week))"}>}Sales) , it does not work.

what is the difference when I put a $(=Min(Snapshot_week) ?

[Digvijay_Singh]

May be try these, didn't do field comparison in set expression for a long time so not sure of exact syntax - 

sum({<year_week = {"=year_week = Snapshot_week"}>}

sum({<year_week = {"=year_week < Snapshot_week"}>}

'$(=)' is mandatory if you have aggregate functions(min/max) after '=' in your set expression. You can search for good set analysis articles in the community, there are plenty to learn from.

[Digvijay_Singh]

May be in your situation you don't need set analysis caz you are doing row by row calculation, so 'if' could be a easier option over here. Row by Row comparison is performance heavy but if you dataset is small and if its the need of the hour then you may go with 'IF' in this case.

[Captain_Jack]

thanks for the reply, 

unfortunately ,it does not work,

I suppose you mean: sum({<year_week = {"=year_week < Snapshot_week"}>}Sales) or ?

because it shows an error in the expression.

[Captain_Jack]

the dataset is big  with 3 years more granular data . not sure if  'if' will take long time to perform.

[Digvijay_Singh · Accepted solution]

I like this below solution appropriate in your situation, create flag in the script, will be very fast  - 

https://community.qlik.com/t5/QlikView-App-Dev/compare-2-fields-set-analysis/m-p/1183007

[Digvijay_Singh]

Set analysis will also check each and every row to to compare two fields and decide to pick the sales from that row or not so I think both will perform equally bad, script based flags would do far better as per the Sunny's post I shared.

[Captain_Jack]

it works , thanks Digvijay for the help !!!