topic Re: How to make dimensions in chart works for function in set analysis in QlikView

How to make dimensions in chart works for function in set analysis

adamgaox — Sat, 24 Sep 2016 01:13:46 GMT

Hi All,

Thanks in advance for your time helping on my issues.

First of all, this question is under a situation that I would like to build a chart with year and month as dimension to aggregate sales amount as metrics.

If I want to calculate the metrics like last 1 month, last 2 month, last 3 month ... to last n month.

To implement this I'm planning two tables in my qlikview backend

one is called Lookup_Table

Lookup_Number	Year	Month	YearMonth
1	1993	1	199301
2	1993	2	199302
...	...	...	...
792	2058	12	205812

Lookup_Number holds the row number from min to max

another is fact table

YearMonth	Sales
199301	1000
199302	1000
...	...
...	...
205812	1000

Sales holds the amount and all values equal 1000

By finishing these two tables I'm starting to build the straight table.

Straight table takes [Year],[Month] as dimension

sum([Sales]) as metric

I was trying first to build last 1 month expression here:

sum({<Lookup_Number={$(=max(Lookup_Number)-1)}>}[Sales])

my issue here is with this inner function in set analysis. This function works under the entire data set

for example

Year Month Last 1 Month

1993 01 0

1993 02 0

... ... 0

2058 11 1000

What I would like to see is:

Year Month Last 1 Month

1993 01 0

1993 02 1000

... ... 1000

2058 11 1000

2058 12 1000

Is there any way to make the dimension outside works for the inner function in set analysis?

Thanks!

Best Regards.

Message was edited by: Adam Gao

Re: How to make dimensions in chart works for function in set analysis

Anil_Babu_Samineni — Sat, 24 Sep 2016 03:02:54 GMT

Try this or please post the application

sum({<Lookup_Number={'=$(=max(Lookup_Number)-1)'}>}[Sales])

Re: How to make dimensions in chart works for function in set analysis

adamgaox — Sat, 24 Sep 2016 03:30:46 GMT

Hi Anil,

I tried your set analysis seems work but result is the same as current month.

I'll also attached the application thanks. !

Re: How to make dimensions in chart works for function in set analysis

sunny_talwar — Sat, 24 Sep 2016 11:14:12 GMT

Are you looking for result as in the second column?

I am using this

=Sum({<Lookup_Num={"$(='<=' & (Max(Lookup_Num)-1))"}>}Sales)

Column 3 is your initial expression and Column 4 is what Anil provided. But this might only work when you have selected a particular year. Is that how you are going to be using this? If not, then you might need to use Aggr() function instead of using set analysis because set analysis is evaluated once per dimension.

But before we go any further lets confirm if this looks right when you have an year selected or not

Re: How to make dimensions in chart works for function in set analysis

Anil_Babu_Samineni — Sat, 24 Sep 2016 14:26:44 GMT

Hey Adam,

You have Forecast data, so this measure applicable for Max Year and Max Month. And you don't have data for selection of sales. Please create master calendar and then try using two expressions

Current Month -- sum({<Month={'=$(=max(Month))'}>}[Sales])

Previous Month -- sum({<Month={'=$(=max(Month)-1)'}>}[Sales])

For ref, Please have a look image

Re: How to make dimensions in chart works for function in set analysis

adamgaox — Sat, 24 Sep 2016 22:08:12 GMT

Hi Sunny,

My intention is to sum last 1 month of sales amount under the year and month dimension, like I have in my following snapshot:

like we have displayed in the 6th column: for each row it contains the total of amount of last month

201601 should be corresponding to 201512 month total sales amount.

and one by one.

In my expression:sum({<Lookup_Number={$(=max(Lookup_Number)-1)}>}[Sales])

the element part $(=max(Lookup_Number)-1) is evaluated under the entire data set

and it's not applied under the year and month dimension I set in the straight table.

for example: if under the year 2016 and month 1 the lookup number should be 277 and minus 1 should be 276

then it will take the data set where lookup number is 276 that's exactly what I want.

I have tested with your expression as well but it's the same and it evaluates with entire data set.

Thanks we can keep discussing to find if it's possible to do like this.

I know there's a way to build a structure to add one more backend table AsofMonth which stores corresponding relationship between months like below:

AsofYearMonth YearMonth Type

201601 201512 LastMonth

201602 201601 LastMonth

...

this is also a good way but I would say if there's some other way to make it easier to understand and build apps without Asof tables.

Best Regards.

Adam.

Re: How to make dimensions in chart works for function in set analysis

adamgaox — Sat, 24 Sep 2016 22:11:38 GMT

Hi Anil,

Would you like to share the idea of Master Calendar and how it's connected with the fact?Maybe in that way I can find some helpful suggestions.

Thanks.

Adam

Re: How to make dimensions in chart works for function in set analysis

sunny_talwar — Sat, 24 Sep 2016 22:50:41 GMT

Try Above() function instead of set analysis here

=Above(Sum({<Year, YearMonth>}Sales)) * Avg(1)

Re: How to make dimensions in chart works for function in set analysis

adamgaox — Sun, 25 Sep 2016 06:39:27 GMT

Hi Sunny,

I would say this is a brilliant way when you only take YearMonth as dimension and only to calculate last 1 month sales amount.

I would mark it as a good solution when client request a requirements to calculate last 1 month.

But if you imagine the following two situation:

1.if we expand other dimension in the future like customer

eg:

current month

customer0 201602 1000

customer1 201601 2000

customer1 201602 3000

last 1 month

customer0 201602 -

customer1 201601 1000

customer1 201602 2000

2.if we need not only last 1 month we even need last n month

Thanks.

Best Regards.

Adam

Re: How to make dimensions in chart works for function in set analysis

sunny_talwar — Sun, 25 Sep 2016 12:50:06 GMT

Adam -

1) I am not sure I understand your example in the first part. But may be you just need to use a Total with multiple dimensions

=Above(TOTAL Sum({<Year, YearMonth>}Sales)) * Avg(1)

This will look above regardless of change in any dimension, whereas if you don't have TOTAL in your expression, the output will restart everytime your second last dimension changes value.

I guess the issue would be if you multiple dimensions and you want to restart based on few of them and be continuous on few of them. Then it tends to get challenging (We can talk about this, if in case you are looking for this kind of thing)

2) I think this should be easy

=Above(Sum({<Year, YearMonth>}Sales), N) * Avg(1)

Replace N with number of months you want to go above.

I think it would be helpful if you read about Above()/Below Function here (and may be also After()/Before())

-> Missing Manual - Above() and Below()

-> Missing Manual - Before() and After()