topic Re: Calculated Dimension in QlikView

Calculated Dimension

Kreyneke — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculated Dimension

rubenmarin — Wed, 15 Apr 2020 10:56:54 GMT

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)

Re: Calculated Dimension

Kreyneke — Thu, 16 Apr 2020 06:27:48 GMT

Hi Rubenmarin,

this worked perfectly thank you

Thank you

Kallie

Re: Calculated Dimension

Kreyneke — Thu, 16 Apr 2020 12:19:33 GMT

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

Re: Calculated Dimension

Kreyneke — Thu, 16 Apr 2020 12:25:35 GMT

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

Re: Calculated Dimension

rubenmarin — Thu, 16 Apr 2020 13:10:47 GMT

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)

Re: Calculated Dimension

Kreyneke — Thu, 16 Apr 2020 13:15:31 GMT

Hi Rubenmarin,

it unfortunately still gives the same calculations.

what does the 2 do if i may ask?

Thank you

Kallie

Re: Calculated Dimension

rubenmarin — Thu, 16 Apr 2020 13:18:10 GMT

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)

Re: Calculated Dimension

Kreyneke — Thu, 16 Apr 2020 13:22:52 GMT

Hi Rubenmarin

unfortunately the values for 2019 is still the same

Thank you

Kallie

Re: Calculated Dimension

rubenmarin — Thu, 16 Apr 2020 13:25:53 GMT

Maybe adding Year field to aggr():

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