topic Set Analysis Expression to apply last month in QlikView

Set Analysis Expression to apply last month

Raj11 — Mon, 23 Jun 2014 13:28:03 GMT

Hi,

I have data like below.

Now I want to create a bar chart like below

blue color bar means: total distict appid that are having status Active

Red Color Bar means: total distinct appid those are having status Active in previous month but Deleted in current month

ex: appid =2, it is active in feb but deleted in mar

Yello Color bar means: total distinct appid those are having status Deletedin previous month but Activein current month

ex: appid = 3

I tried this expression:

count

({<Status = {"Active"},Appid = P({<Loaddate={"$(=date(above(only(Loaddate),1),'DD-MMM-YYYY'))"},Status = {"Deleted"}>} Appid)>}DISTINCT Appid)

But this is not working and

date(above(only(Loaddate),1),'DD-MMM-YYYY')

it is giving the previous month.

Please anybody helps me how to acheive the above described.

Thanks in advance,

Niranjan M.

Gysbert_Wassenaar — Tue, 24 Jun 2014 08:58:18 GMT

See attached qvw

Raj11 — Tue, 24 Jun 2014 09:11:30 GMT

Hi All,

Thanks Gysbert Wassenaar

I go tthe solution for the above,sloved it by combination of both script and set analysis.

Data:

Load

Appid,Loaddate,Status, Appid & Month(Loaddate) & Year (Loaddate) as Key

from ....<file>;

Counts:

Load

Key,

Lookup('Status','Key',Appid & Month(AddMonths(Loaddate,-1)) & Year(AddMonths(Loaddate,-1))) As LastMonth_App_Status,

Resident

Data;

Use the below expression for the bar chart:

blue: count ({<Status={"Active"}>} Appid)

Red: Count({<LastMonth_App_Status={"Active"},Status={"Deleted"}>} Appid)

Yellow: Count({<LastMonth_App_Status={"Deleted"},Status={"Active"}>} Appid)

Thanks to all

Niranjan M.