topic Re: different result when a specific date is selected in pivot table in QlikView

different result when a specific date is selected in pivot table

sadiaasghar — Wed, 25 Nov 2020 16:16:04 GMT

i am calculating market share for all dates of current month but if a specific date is selected the table gives different result for the same date. as you can see in screenshot if I selected a specific Company and a date '02/10/2020' it' giving market share 0.56 in above table and in lower table it's 13.35 which is the correct market share for the date. I want to show correct figures for all the dates of the month in above table. I am not getting what is wrong because formula in both tables is same. I am attaching qvw file for reference.

Re: different result when a specific date is selected in pivot table

sadiaasghar — Tue, 20 Oct 2020 10:49:45 GMT

@Kushal_Chawda @sunny_talwar Please have a look

Re: different result when a specific date is selected in pivot table

Kushal_Chawda — Tue, 20 Oct 2020 11:33:49 GMT

@sadiaasghar see the attached

Re: different result when a specific date is selected in pivot table

sadiaasghar — Tue, 20 Oct 2020 11:56:00 GMT

Can you please explain what did you do in the attached solution except total<AppDate> in upper table?

Re: different result when a specific date is selected in pivot table

sadiaasghar — Thu, 22 Oct 2020 04:00:07 GMT

@Kushal_Chawda it didn't solve my problem.

Also I have to calculate cumulative sum

Simple sum Expression is working but when I apply rangesum formula to caculate Cumulative it's returning 0.

Sum Expression:

SUM(TOTAL<[Invoice Date_FKDAT]>aGGR(((Sum({<RecType={'Sales'},[Posting Status_RFBSK]={'C'},[FISCAL YEAR] = {$(=Max([FISCAL YEAR]))},[MonthNo] = {$(=Match(Month(Date(Max([Invoice Date_FKDAT]))), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))}
>}[SalesMTon])))
-
((((Sum({<RecType={'Sales'},[Posting Status_RFBSK]={'C'},[FISCAL YEAR] = {$(=Max([FISCAL YEAR]))},[MonthNo] = {$(=Match(Month(Date(Max([Invoice Date_FKDAT]))), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))},
[Billing Type_FKART]={'S1','RE','ZRAX','ZREX','ZRFX','ZRFI','G2','ZRCO'}>}[SalesMTon]))))*2),[Invoice Date_FKDAT]))

Cumulative sum Expression:

=Rangesum(above(total
(fabs(Sum({<RecType={'Sales'},[Posting Status_RFBSK]={'C'},[FISCAL YEAR] = {$(=Max([FISCAL YEAR]))},[MonthNo] = {$(=Match(Month(Date(Max([Invoice Date_FKDAT]))), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))}>}[SalesMTon])))
-
(((fabs(Sum({<RecType={'Sales'},[Posting Status_RFBSK]={'C'},[FISCAL YEAR] = {$(=Max([FISCAL YEAR]))},[MonthNo] = {$(=Match(Month(Date(Max([Invoice Date_FKDAT]))), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))},[Billing Type_FKART]={'S1','RE','ZRAX','ZREX','ZRFX','ZRFI','G2','ZRCO'}>}[SalesMTon]))))*2),0,RowNo(total)))

Re: different result when a specific date is selected in pivot table

sadiaasghar — Thu, 22 Oct 2020 07:00:11 GMT

I have updated my qlikview document. I have two measures in the same expression.

IF(Company='ABC',
((((Sum({<RecType={'Sales'},[Posting Status_RFBSK]={'C'},[FISCAL YEAR] = {$(=Max([FISCAL YEAR]))},[MonthNo] = {$(=Match(Month(Date(Max([Invoice Date_FKDAT]))), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))}
,[Invoice Date_FKDAT]={">=$(=Date(vMinDates,'DD/MM/YYYY'))<=$(=Date(vMaxDates,'DD/MM/YYYY'))"}>}[SalesMTon])))
-
((((Sum({<RecType={'Sales'},[Posting Status_RFBSK]={'C'},[FISCAL YEAR] = {$(=Max([FISCAL YEAR]))},[MonthNo] = {$(=Match(Month(Date(Max([Invoice Date_FKDAT]))), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))},
[Invoice Date_FKDAT]={">=$(=Date(vMinDates,'DD/MM/YYYY'))<=$(=Date(vMaxDates,'DD/MM/YYYY'))"},[Billing Type_FKART]={'S1','RE','ZRAX','ZREX','ZRFX','ZRFI','G2','ZRCO'}>}[SalesMTon]))))*2)))

/
(((((Sum({<RecType={'Sales'},[Posting Status_RFBSK]={'C'},[FISCAL YEAR] = {$(=Max([FISCAL YEAR]))},[MonthNo] = {$(=Match(Month(Date(Max([Invoice Date_FKDAT]))), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))}
,[Invoice Date_FKDAT]={">=$(=Date(vMinDates,'DD/MM/YYYY'))<=$(=Date(vMaxDates,'DD/MM/YYYY'))"}>}[SalesMTon])))
-
((((Sum({<RecType={'Sales'},[Posting Status_RFBSK]={'C'},[FISCAL YEAR] = {$(=Max([FISCAL YEAR]))},[MonthNo] = {$(=Match(Month(Date(Max([Invoice Date_FKDAT]))), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))},
[Invoice Date_FKDAT]={">=$(=Date(vMinDates,'DD/MM/YYYY'))<=$(=Date(vMaxDates,'DD/MM/YYYY'))"},[Billing Type_FKART]={'S1','RE','ZRAX','ZREX','ZRFX','ZRFI','G2','ZRCO'}>}[SalesMTon]))))*2)))
+
sum({<AppDate={">=$(=Date(vMinDates,'DD/MM/YYYY'))<=$(=Date(vMaxDates,'DD/MM/YYYY'))"},Company={'*'}>}TOTAL AppVolume))
,Sum({<AppDate={">=$(=Date(vMinDates,'DD/MM/YYYY'))<=$(=Date(vMaxDates,'DD/MM/YYYY'))"}>}AppVolume)
/
(((((Sum({<RecType={'Sales'},[Posting Status_RFBSK]={'C'},[FISCAL YEAR] = {$(=Max([FISCAL YEAR]))},[MonthNo] = {$(=Match(Month(Date(Max([Invoice Date_FKDAT]))), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))}
,[Invoice Date_FKDAT]={">=$(=Date(vMinDates,'DD/MM/YYYY'))<=$(=Date(vMaxDates,'DD/MM/YYYY'))"}>}[SalesMTon])))
-
((((Sum({<RecType={'Sales'},[Posting Status_RFBSK]={'C'},[FISCAL YEAR] = {$(=Max([FISCAL YEAR]))},[MonthNo] = {$(=Match(Month(Date(Max([Invoice Date_FKDAT]))), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))},
[Invoice Date_FKDAT]={">=$(=Date(vMinDates,'DD/MM/YYYY'))<=$(=Date(vMaxDates,'DD/MM/YYYY'))"},[Billing Type_FKART]={'S1','RE','ZRAX','ZREX','ZRFX','ZRFI','G2','ZRCO'}>}[SalesMTon]))))*2)))
+
sum({<AppDate={">=$(=Date(vMinDates,'DD/MM/YYYY'))<=$(=Date(vMaxDates,'DD/MM/YYYY'))"},Company={'*'}>}TOTAL AppVolume))

Re: different result when a specific date is selected in pivot table

Brett_Bleess — Wed, 04 Nov 2020 16:32:53 GMT

What would be most helpful is if you can clarify exactly what it is you still need help with at this point, as I have no clue based upon your last two posts, and that is why I suspect others have not replied as well, you need to clarify what it is you need at this point to get further responses.

Regards,
Brett