Rolling up not working

[Anonymous]

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

[sunny_talwar]

For your current selection, what is your expected output?

[Anonymous]

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.

[HirisH_V7]

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

[Anonymous]

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.

[sunny_talwar · Accepted solution]

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))

[Anonymous]

Hi Sunny,

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

[sunny_talwar]

Awesome, I am glad I was able to help