topic Re: SET Expression Not Working in QlikView

SET Expression Not Working

Lakshminarayanan_J — Sat, 16 Nov 2024 17:56:58 GMT

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.

@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 !

Re: SET Expression Not Working

tm_burgers — Wed, 21 Oct 2020 11:56:50 GMT

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

Re: SET Expression Not Working

tm_burgers — Wed, 21 Oct 2020 11:58:42 GMT

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.

Re: SET Expression Not Working

Lakshminarayanan_J — Mon, 09 Nov 2020 12:32:47 GMT

Hi @tm_burgers ,

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