Calculated Dimension

[Kreyneke]

Dear All,

i am trying to create a graph/ table to show the value above 30k and number of stores and below 10k number of stores.

The formula i am using is calculating the values but i am not sure how to limit it above 30k

=aggr(
(sum({<Year = {"$(=MaxString(Year))"}>}rangesum(Lens_Total_Net) * SETTLEMENT_%)) ,
CSTM_CD) 

it is also breaking up the totals into different values instead of just one sum?

any help would be greatly appreciated.

Thank you

Kallie

[rubenmarin · Accepted solution]

Hi, you can try with:

=aggr(
IF(sum({<Year = {"$(=MaxString(Year))"}>}rangesum(Lens_Total_Net) * SETTLEMENT_%)>30000, '>30K'
  ,IF(sum({<Year = {"$(=MaxString(Year))"}>}rangesum(Lens_Total_Net) * SETTLEMENT_%)>10000, '>10K')) ,
CSTM_CD) 

[Kreyneke]

Hi Rubenmarin,

this worked perfectly thank you

Thank you

Kallie

[Kreyneke]

Hi Rubenmarin,

From initial looks the calcualted dimension is working correctly. but it is under the expression count it is not actually totalling up correctly.

Below i used a excel sheet to calculate it uses the same expressions as in the calculated graph and the totals are different.

count in attached sheet is >30 - 217

>10 - 148

how ever qlikview is calculating 

>30 - 215

>10 - 139

this is under the 2019 tab.

Would you be able to assist me with this?

Thank you

Kallie

[Kreyneke]

Hi Ruben,

i just realized why it is calculating correctly for 2020 but not for 2019.

in the calculated dimension we have (=MaxString(Year) which is current year. but in my expression i am adding in 2019 in as well. which is then adding them up wrong

any idea of how i can fix that?

Thank you

Kallie

[rubenmarin]

Can you try if this works?:

=aggr(
IF(sum({<Year = {"$(=MaxString(Year))","$(=MaxString(Year),2)"}>}rangesum(Lens_Total_Net) * SETTLEMENT_%)>30000, '>30K'
  ,IF(sum({<Year = {"$(=MaxString(Year))","$(=MaxString(Year),2)"}>}rangesum(Lens_Total_Net) * SETTLEMENT_%)>10000, '>10K')) ,
CSTM_CD)

[Kreyneke]

Hi Rubenmarin,

it unfortunately still gives the same calculations.

what does the 2 do if i may ask?

Thank you

Kallie

[rubenmarin]

Hi Kallie, I post it wrong, it should be a parameter of Maxstring(), 2 means to get the second max value

=aggr(
IF(sum({<Year = {"$(=MaxString(Year))","$(=MaxString(Year,2))"}>}rangesum(Lens_Total_Net) * SETTLEMENT_%)>30000, '>30K'
  ,IF(sum({<Year = {"$(=MaxString(Year))","$(=MaxString(Year,2))"}>}rangesum(Lens_Total_Net) * SETTLEMENT_%)>10000, '>10K')) ,
CSTM_CD)

[Kreyneke]

Hi Rubenmarin

unfortunately the values for 2019 is still the same

Thank you

Kallie

[rubenmarin]

Maybe adding Year field to aggr():

=aggr(
IF(sum({<Year = {"$(=MaxString(Year))","$(=MaxString(Year,2))"}>}rangesum(Lens_Total_Net) * SETTLEMENT_%)>30000, '>30K'
  ,IF(sum({<Year = {"$(=MaxString(Year))","$(=MaxString(Year,2))"}>}rangesum(Lens_Total_Net) * SETTLEMENT_%)>10000, '>10K')) ,
CSTM_CD, Year)

If you could upload a sample I could make some tries.