Value for Repeating Dates

[mhmmd_srf]

Hi All,

I have an requirement like :

I need to check Order Count for last 90 days each. If that day have some transaction it will show as 0,else 1. We need to perform the similar activity for last 90 days and sum them up. In detail the expression would be:

if(Sum({$<M_DATE={"$(=(Date(Date#('$(vDATE)','M/D/YYYY') - 90)))"}>} ORD_LINE_CNT)>0'0','1')

+

if(Sum({$<M_DATE={"$(=(Date(Date#('$(vDATE)','M/D/YYYY') - 89)))"}>} ORD_LINE_CNT)>0'0','1')

+

if(Sum({$<M_DATE={"$(=(Date(Date#('$(vDATE)','M/D/YYYY') - 88)))"}>} ORD_LINE_CNT)>0'0','1')

+

.

.

.

+

if(Sum({$<M_DATE={"$(=(Date(Date#('$(vDATE)','M/D/YYYY') - 1)))"}>} ORD_LINE_CNT)>0'0','1')

It is very difficult to write this code for all days. Do we have any alternate way to do this in short way.

Please help.

We need to show this Single KPI in Qlik Sense.

Thanks,

Sarif

[swuehl]

Maybe something like this using advanced aggregation

=Sum( {$<M_DATE={">=$(=(Date(Date#('$(vDATE)','M/D/YYYY') - 90)))<=$(=(Date(Date#('$(vDATE)','M/D/YYYY') - 1)))"}>}

    Aggr( If( Sum(ORD_LINE_CNT)>0,0,1)

          , M_DATE // Add potential other dimensions like your chart dimension

          )

)

[mhmmd_srf]

Hello Stefan,

The above one was simpler version of expression.

Actually my real expression is:

If((Sum({$<Holiday_HD ={'N'}, PLANT_HD = {'All'},M_DATE={"$(=(Date(Date#('$(vDATE)','M/D/YYYY') - XX)))"}>} ORD_LINE_CNT) +

Sum({$<,Holiday_HD ={'Y'}, PLANT_HD -= {'All'} ,M_DATE={"$(=(Date(Date#('$(vDATE)','M/D/YYYY') - XX)))"}>} ORD_LINE_CNT))>0,'0','1')

How to set this in your expression. Can you please help me.

Thanks,

Sarif