Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to assign the most recent cluster to historical data

Hi,

I have this problem.

I would like to assign to Customers their most recent Cluster (based on a calendar selection) and made calculation on their historical values, disregarding their previous cluster.

In attached a simple example for one Customer.

My aim is create a bar chart with Cluster as Dimension and Sum(qta actual)/Sum(qta prevoius) as expression (where actual means selected dates and prevoius means the corresponding dates of previous year).

Thanks a lot!

Elena

1 Solution

Accepted Solutions
Kushal_Chawda

Please see the attached. This will help.

View solution in original post

10 Replies
Anonymous
Not applicable
Author

in this: Sum(qta actual)/Sum(qta prevoius)

you mean current year v/s previuos year?

or

Current year date v/s Previous year data?

or

Current Year Month v/s Previous year month

Anonymous
Not applicable
Author

I mean Current Year Month v/s Previous year month

Thank you!

Elena

Anonymous
Not applicable
Author

Take Cluster as a dimension, and expression is:

For Current Year v/s Previous Year : 

=sum({<YearField={'=$(=max(YearField)'}>} QTA)/sum({<YearField={'=$(=(max(YearField)-1)'}>} QTA)


for rest see these threads:

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

Comparing Current Month and Previous Month Sales

Anonymous
Not applicable
Author

It's not a problem related to point in time reporting.

It's related to the change of cluster.... I what the most recent cluster even if I consider values of other past cluster for the same customer.

Thank you

Elena

sunny_talwar

First of all, your clusters are not matching up with your expected output and keeping that in mind the results won't match, but see if this helps:

This period:

=Sum({<Date ={"$(='>=' & Date(Min(Date), 'YYYYMM') & '<=' & Date(Max(Date), 'YYYYMM'))"}>}Qta)

Previous period:

=Sum({<Date ={"$(='>=' & Date(AddYears(Min(Date), -1), 'YYYYMM') & '<=' & Date(AddYears(Max(Date), -1), 'YYYYMM'))"}>}Qta)

I think I got it now. Let me see if I can figure it out.

sunny_talwar

Try these two expressions:

Current

=If(Cluster = FirstSortedValue(TOTAL Cluster, -Date), Sum(TOTAL{<Date ={"$(='>=' & Date(Min(Date), 'YYYYMM') & '<=' & Date(Max(Date), 'YYYYMM'))"}>}Qta))

Previous

=If(If(Cluster = FirstSortedValue(TOTAL Cluster, -Date), Sum(TOTAL{<Date ={"$(='>=' & Date(Min(Date), 'YYYYMM') & '<=' & Date(Max(Date), 'YYYYMM'))"}>}Qta)) > 0,

Sum(TOTAL {<Date ={"$(='>=' & Date(AddYears(Min(Date), -1), 'YYYYMM') & '<=' & Date(AddYears(Max(Date), -1), 'YYYYMM'))"}>}Qta))

Output Screenshots:

1)

Capture.PNG

2)

Capture.PNG

3)

Capture.PNG

Kushal_Chawda

Please see the attached. This will help.

sunny_talwar

Nice way of doing it Kush