SET Expression Not Working

[Lakshminarayanan_J]

Hi ALL,

Need to implement the below logic.

 Stock Logic: Current month closing value/last 3 months Avg secondary sales value /30

Expression Used: num(Sum({<Month={$(=Month)}>}(Closing_Value/1000))
/ (sum({<Month={">=$(=max(Month)-2)<=$(=max(Month))"}>}(Sales_Value/1000))/3)*30,'#,##0')

When Selecting the Month Data is Displaying Correctly,

Issue : When month is not selected it need to display the Stock data for all the month ,but it is not displaying data correctly ,Kindly help on sorting this issue.

QlikView @sunny_talwar @kdr_Qv @tm_burgers 

Please find the below table with current logic and expected data:

Month	Closing Value	Sales Value	Stock Expression:(Current Logic) num(Sum({<Month={$(=Month)}>}(Closing_Value/1000)) / (sum({<Month={">=$(=max(Month)-2)<=$(=max(Month))"}>}(Sales_Value/1000))/3)*30,'#,##0')	Expected Data
Jan	1,306,376	1,695,429	-	69
Feb	1,320,013	1,786,609	-	34
Mar	1,146,266	1,948,894	-	19
Apr	1,278,724	1,528,215	-	22
May	1,271,170	1,502,994	-	23
Jun	1,338,675	1,627,301	-	26
Jul	1,272,925	1,707,120	0	24
Aug	1,525,346	2,132,017	0	25
Sep	1,549,959	2,184,524	0	23

Kindly help on this !

[tm_burgers · Accepted solution]

Hey JLVS, 

The below expression has worked for me in my test environment; you can wrap it in num() if you would like, but can also just use the number formatting for the measure too.

(Sum(Closing_Value/1000)/((Sum(Aggr(
RangeSum(Above(Sum(Sales_Value), 0, 3))
, Month))/1000)/3))*30

[tm_burgers]

Sorry @Lakshminarayanan_J ; I didn't see that this was posted in QlikView, I assume the expression will still work but I do not have QV to test it. 

[Lakshminarayanan_J]

Hi @tm_burgers ,

Apologies for delay response above expression is working fine for month dimention