Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum of distinct counts based on dates selection

Hi,

I need to calculate the sum of distinct counts of a variable( See the below sample data). this is a pivot data, but original data is based on the dates for more records based on a unique id.For the current month, I'm getting the sum based on the below syntax. When I again write the same syntax to get the previous month i.e. max of current month - 1 = Previous month, then also my sum is giving me the current month sum of distinct counts instead of last month counts.

SUM OF DISTINCT COUNTS FOR CURRENT MONTH:

NEW_MEND_DATE = Date(MonthStart([MEND_DT]), 'MMM-YYYY') as NEW_MEND_DATE

vmin_mendate =Min(Date([NEW_MEND_DATE], 'MMM-YYYY'))

= sum(Aggr(count((WRITE_OFF)),NEW_MEND_DATE={">=$(vmin_mendate)=$(vmax_mendate))"}))

SUM OF DISTINCT COUNTS FOR PREVIOUS MONTH:

NEW_MEND_DATE = Date(MonthStart([MEND_DT]), 'MMM-YYYY') as NEW_MEND_DATE

vmin_mendate =Min(Date([NEW_MEND_DATE], 'MMM-YYYY'))

vmax_mendate_prev = =Date(Max([NEW_MEND_DATE]) - 1, 'MMM-YYYY')

= sum(Aggr(count((WRITE_OFF)),NEW_MEND_DATE={">=$(vmin_mendate)=$(vmax_mendate_prev))"}))

DATE FORMAT OF NEW_MEND_DATE AND MY VARIABLE

1 Solution

Accepted Solutions
sunny_talwar

Are you sure this is working?

Capture.PNG

Doesn't look like the correct syntax.... are you may be looking for thie

CURRENT MONTH

Count({<NEW_MEND_DATE = {"$(=Date(Max(NEW_MEND_DATE), 'MMM-YYYY'))"}>}WRITE_OFF)

PREVIOUS MONTH

Count({<NEW_MEND_DATE = {"$(=Date(MonthStart(Max(NEW_MEND_DATE), -1), 'MMM-YYYY'))"}>}WRITE_OFF)

View solution in original post

6 Replies
sunny_talwar

Are you sure this is working?

Capture.PNG

Doesn't look like the correct syntax.... are you may be looking for thie

CURRENT MONTH

Count({<NEW_MEND_DATE = {"$(=Date(Max(NEW_MEND_DATE), 'MMM-YYYY'))"}>}WRITE_OFF)

PREVIOUS MONTH

Count({<NEW_MEND_DATE = {"$(=Date(MonthStart(Max(NEW_MEND_DATE), -1), 'MMM-YYYY'))"}>}WRITE_OFF)

Not applicable
Author

hi Sunny,

yes I did my syntax as below and was working fine. Thanks for sending it to me.

=(count({<NEW_MEND_DATE={">=$(vmin_mendate) <=$(vmax_mendate)"}>} WRITE_OFF)
-
count({<NEW_MEND_DATE={">=$(vmin_mendate) <=$(vmax_mendate_PREV)"}>} WRITE_OFF)
)

Thanks

Sowjanya

Not applicable
Author

Hi Sunny,

Can you also help me with the below please.

Can we create a TREND LINE / CHART in a column based on month data into a pivot table? Below is the sample data. In column G I want to add a trend line showing the performance of the actual vs target. please advise if this is possible?

thanks

Sowjanya

sunny_talwar

For each Dummy variable you want to construct a trend line?

Not applicable
Author

Yes. For each row  I need to add trend line for all the months

for eg.

c-4 , c10, c13 ---------------for all the 4 months I need to see the performance of actual in form of trend line graph.

Something like the below.