Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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
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
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.
Hi @tm_burgers ,
Apologies for delay response above expression is working fine for month dimention