Rolling over 45 days

[shwetagupta]

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

[mdmukramali · Accepted solution]

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.