Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Set Analysis Expression to apply last month

Hi,

I have data like below.

AppidLoaddateStatus
2Jan-13Active
3Jan-13Deleted
1Feb-13Deleted
2Feb-13Active
3Feb-13Active
1Mar-13Active
2Mar-13Deleted
3Mar-13Active

Now I want to create a bar chart like below

Chart.png

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.

1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

Re: Set Analysis Expression to apply last month

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.

View solution in original post

2 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Set Analysis Expression to apply last month

See attached qvw


talk is cheap, supply exceeds demand
Highlighted
Contributor III
Contributor III

Re: Set Analysis Expression to apply last month

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.

View solution in original post