Solved: add month id + lock period - Qlik Community

stephenedberkg · ‎2016-01-20

Each id have different lock period.i want to add current month selection id + the lock period.

=num(Sum({$< Year = {$(=Max(Year))},MonthID={$(=Max(MonthID) +OrderLockPeriod )},month=

> } Sales),0.00)

i used above expression

its working fine when i selected the id .

if i not selected any id its not working its displaying zero.

because of the different lock period only its displaying zero.

i want to display the values without select any id too.

how can i achieve this?

help me?

sunny_talwar · ‎2016-01-20

This:

=Sum({<Year={$(=Max(Year))}, Months, MonthID = {"$(=Max(MonthID))"}>} Aggr(If(Only({1}MonthIDS) = Max(TOTAL <ID> MonthID) + Only(TOTAL <ID> [lock period]), Sum({<Year={$(=Max(Year))}, Months, MonthID = {"$(=Max(MonthID))"}>}sales)), ID, MonthIDS))

View solution in original post

sunny_talwar · ‎2016-01-20

Which lockperiod would you want to see if you have not selected anything? Max?

=Num(Sum({$<Year = {$(=Max(Year))},MonthID={$(=Max(MonthID) +Max(OrderLockPeriod))},month = >} Sales),0.00)

stephenedberkg · ‎2016-01-20

thanks for your reply sunindia

my order lock period is 0,2,3

it depends on the id.

if nothing is selected it should display all the id values with their lock periods.

sunny_talwar · ‎2016-01-20

The issue then is that Set Analysis is evaluated once per chart. You cannot have OrderLockPeriod evaluated to a different period for individual ids using set analysis. May be you can try with if statement

=Num(Sum({$<Year = {$(=Max(Year))},month = >} If(MonthID = (Max(MonthID) + OrderLockPeriod), Sales)),0.00)

I have not tested this out, but you might need a Aggregate function within the if.

stephenedberkg · ‎2016-01-20

thanks for your help sunindia

= Num(Sum({$<Year = {$(=Max(Year))},Month=,Year=>}If (Aggr (MonthID = (Max(MonthID) + OrderLockPeriod),id), Arrivals)),0.00)

i tried like this but its not working

sunny_talwar · ‎2016-01-20

Give this a shot:

=Num(Sum({$<Year = {$(=Max(Year))},month = >} If(MonthID = Aggr((Max(TOTAL MonthID) + Only(OrderLockPeriod)), ID), Sales)), '0.00')

or

=Num(Sum({$<Year = {$(=Max(Year))},month = >} If(Aggr(MonthID = (Max(TOTAL MonthID) + Only(OrderLockPeriod)), ID), Sales)), '0.00')

Might be able to help you better if you are able to share a sample

stephenedberkg · ‎2016-01-20

Dear sunindia

this is also not working .

=Num(Sum({$<Year = {$(=Max(Year))},month = >} If(MonthID = Aggr((Max(TOTAL MonthID) + Only(OrderLockPeriod)), ID), Sales)), '0.00')

=Num(Sum({$<Year = {$(=Max(Year))},MonthID={$(=Max(MonthID) +Max(OrderLockPeriod))},month = >} Sales),0.00)

this is working fine but with out select any id it show the lock period 3 sales only.

sunny_talwar · ‎2016-01-20

Would you be able to share a sample?

stephenedberkg · ‎2016-01-20

Dear Sunny T

i attached the sample app.

sunny_talwar · ‎2016-01-20

Try this expression:

=Sum({<Year={$(=Max(Year))}, Months>} Aggr(If(Only({1}MonthIDS) = Max(TOTAL <ID> MonthID) + Only(TOTAL <ID> [lock period]), Sum({<Year={$(=Max(Year))}, Months>}sales)), ID, MonthIDS))