Discussion Board for collaboration related to QlikView App Development.
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
Are you sure this is working?
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)
Are you sure this is working?
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)
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
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
For each Dummy variable you want to construct a trend line?
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.
Check this out