Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, Guys!!!
Need Your Help with following problem!
Describing my situation!!!
I need to show in diogram (any) the information about the products wich were sold in LastMonth better than in PreviousMonth!
For example:
Product Jan Feb March Apr May June
Opel 3 5 1 13 13 11
Mazda 12 11 5 17 16 21
Nissan 31 15 21 33 43 55
Audi 31 51 23 22 7 19
As you can see The LastMonth here is June. Mazda, Nissan, Audi are the products wich were sold better in LastMonth than in PreviosMonth. Because of Opel was sold worse in LastMonth, I should't show it in my final diogram!
I want to get this type of diogram.
Product Jan Feb March Apr May June
Mazda 12 11 5 17 16 21
Nissan 31 15 21 33 43 55
Audi 31 51 23 22 7 19
I have 2 variables. MaxMonthVar and PreviosMonthVar! I thought, If I'll use expression "IF-ELSE" construction, can I get what I want?
MaxMonthVar=MONTH(MAX([SaleDate])).
PreviousMonthVar==MONTH(ADDMONTHS(MAX([SaleDate]),-1))
Something like This:
IF ( ALT(SUM({<Month={'$(MaxMonthVar)'}>}Quantity),0)>ALT(SUM({<Month={'$(PreviousMonthVar)'}>}Quantity),0) ) THAN "SHOW THE RESULT WICH I NEED" ELSE (...)
I assume that your expressions
SUM({<Month={'$(MaxMonthVar)'}>}Quantity)
resp.
SUM({<Month={'$(PreviousMonthVar)'}>}Quantity)
do already work, since Qlik might be a bit pickywith the set expression syntax. If they don't, see also
The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync
Then you can filter your Products (that's what you basically want to do) using set analysis with an advanced search / expression search:
=Sum(
{<Product = {"=SUM({<Month={'$(MaxMonthVar)'}>}Quantity) >SUM({<Month={'$(PreviousMonthVar)'}>}Quantity) "} >}
Quantity)
Using this expression in a chart (e.g. straight table or pivot table) with dimensions Product and Month should return what you requested.
I assume that your expressions
SUM({<Month={'$(MaxMonthVar)'}>}Quantity)
resp.
SUM({<Month={'$(PreviousMonthVar)'}>}Quantity)
do already work, since Qlik might be a bit pickywith the set expression syntax. If they don't, see also
The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync
Then you can filter your Products (that's what you basically want to do) using set analysis with an advanced search / expression search:
=Sum(
{<Product = {"=SUM({<Month={'$(MaxMonthVar)'}>}Quantity) >SUM({<Month={'$(PreviousMonthVar)'}>}Quantity) "} >}
Quantity)
Using this expression in a chart (e.g. straight table or pivot table) with dimensions Product and Month should return what you requested.
Excellent, Stefan! Your method was helpful for me! Thanks a LOT!