Rolling over 45 days

shwetagupta — Sat, 16 Dec 2017 21:38:29 GMT

Hi Folks,

Need help!

I am stuck at two different things on Qlik Sense

1. I have to calculate stock rolling over 45 days from maximum date

Count({<Date={'>=$(vRolling45)<=$(vMaxim)'}>} Distinct SerialNo) // This is done.

where vMaxim =Date((Max(Date)),'DD-MM-YYYY')

vRolling45=Date((Max(Date)-45),'DD-MM-YYYY')

but now I have to calculate rolling over 45 days from maximum date -1

For now i have created another variable

vMaxim_1 =Date((Max(Date)-1),'DD-MM-YYYY')

vRolling45_1=Date((Max(Date)-46),'DD-MM-YYYY')

expression changed to this:

Count({<Date={'>=$(vRolling45_1)<=$(vMaxim_1)'}>} Distinct SerialNo)

Similarly,

Count({<Date={'>=$(vRolling45_2)<=$(vMaxim_2)'}>} Distinct SerialNo)

Count({<Date={'>=$(vRolling45_3)<=$(vMaxim_3)'}>} Distinct SerialNo)

But I don't want to create these many variable, Can someone help me out to achieve this in any other way

Problem 2:

Now I want to show this on bar chart where my dimension will be dates of the current month

value for maximum date(15-12-2017)

Count({<Date={'>=$(vRolling45)<=$(vMaxim)'}>} Distinct SerialNo) // Stock as on date from last 45 days

Value obtained from following expression on maximum date -1(14-12-2017)

Count({<Date={'>=$(vRolling45_1)<=$(vMaxim_1)'}>} Distinct SerialNo) //Stock as on previous date from its last 45 days

and so on for the running month...

How can I achieve this?

Thanks in advance!!!

Re: Rolling over 45 days

mdmukramali — Sun, 17 Dec 2017 10:37:19 GMT

Hi,

Kindly find the attached Sample working application,

I followed the concept of AsOf tables.

i hope the sample application will help you .

if you have any issues please attach the sample data so that we can help you in better way,

Thanks,

Mukram.