topic Re: Rolling up not working in QlikView

Rolling up not working

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

Hi Qlik Gurus,

i'm a little stuck; i am unable to display a rolled up of my data.

the objective of my dashboard is to show all customers that have no sales in the current year when compared against the same period last year.

i think i have this working if we view it by the individual customers.

however, when i tried to use the Cluster as the dimension, it does not work.

i understand the set analysis is based on per customer; appreciate your advise on how/what i can do to be able to display the table to use Customer and another by Cluster.

Please see attached sample file.

thank you for your kind help and suggestions.

regards

Derrell

Re: Rolling up not working

sunny_talwar — Tue, 13 Sep 2016 05:19:52 GMT

For your current selection, what is your expected output?

Re: Rolling up not working

Anonymous — Tue, 13 Sep 2016 05:36:36 GMT

Hi Sunny,

condition 1:

- the idea is to roll up the Sales values in 2014 that does not have sales in 2015.

at the moment it is at the customer level. i would like to be able to roll it up by cluster level then allow users to drill into each of the cluster's customers that meets condition 1.

Re: Rolling up not working

HirisH_V7 — Tue, 13 Sep 2016 06:30:02 GMT

Hi,

are you looking like this or what,

Comparing Both years data and Fetching current year customers who are not having sales,

Check this,

PFA,

HTH,

Hirish

Re: Rolling up not working

Anonymous — Tue, 13 Sep 2016 23:17:50 GMT

Hi Hirish,

thanks for your reply.

i would like to group by Clusters.

once user click on the cluster, it should drill down to the respective customers.

i think this is not possible due to the IF statements.

Re: Rolling up not working

sunny_talwar — Wed, 14 Sep 2016 01:06:43 GMT

This?

Expression:

Sum({<Period>} Aggr(If(Sum({$<Period=, Date = {'>$(=MonthEnd(addmonths(min(Period),-13)))<=$(=MonthEnd(addmonths(max(Period), -12)))'}>} Sale) >= 0 and Sum(Sale) = 0,

Sum({$<Period=, Date = {'>$(=MonthEnd(addmonths(min(Period),-13)))<=$(=MonthEnd(addmonths(max(Period), -12)))'}>} Sale), Null()), Cluster, Customer))

Re: Rolling up not working

Anonymous — Wed, 14 Sep 2016 01:09:09 GMT

Hi Sunny,

yes! your set analysis looks spot on! thank you.

Re: Rolling up not working

sunny_talwar — Wed, 14 Sep 2016 01:47:12 GMT

Awesome, I am glad I was able to help