-
Re: Percentages of increments in pivot tables with calculated dimensions
Bill Markham Jan 7, 2018 1:17 PM (in response to Anibal Martinez-Sistac)The values in your field Date go from 01.05.17 to 08.05.17 [i.e. all in month 5, May]
Whereas the values you enter for your variables go from 03.10.17 to 08.10.2017 [i.e. all in month 10, Oct]
It also looks as if your data field Date is a string and not a numeric Qlik date.
The Calculated Dimension contains things like :
if( (Date>=Date(Date#('$(vBDA_1)','DD.MM.YY'),'DD.MM.YY'))
So :
- Date is a string
- Date(Date#('$(vBDA_1)','DD.MM.YY'),'DD.MM.YY')) is a numeric Qlik date
This is all most confusing.
Maybe when you load the data from [lib://BI RESOURCES/SAMPLE DATA SET.xlsx] you should convert field Date into a numeric Qlik date.
Whilst this does not answer your actual question in the slightest, I do think the Date field needs sorting before you can progress and then maybe post a revised sample qvf.
-
Re: Percentages of increments in pivot tables with calculated dimensions
Anibal Martinez-Sistac Jan 7, 2018 3:05 PM (in response to Bill Markham )Hi Bill. Thank you for your reply. I will go through your comments and suggestions:
"The values in your field Date go from 01.05.17 to 08.05.17 [i.e. all in month 5, May]. Whereas the values you enter for your variables go from 03.10.17 to 08.10.2017 [i.e. all in month 10, Oct]"
Yes. My idea is creating an interactive report. No matter what dates you enter through the 6 Variable Extension boxes, the pivot table will calculate the increases of the correspondent measures (Sales, UABs, Benefits), considering the registries that fall into these 3 categories: "Before","During", and "After". The dates that the user specify through the boxes don´t need to cover the entire Date window of the Data Set.
"It also looks as if your data field Date is a string and not a numeric Qlik date. [...]. Maybe when you load the data from [lib://BI RESOURCES/SAMPLE DATA SET.xlsx] you should convert field Date into a numeric Qlik date."
I have done the following. I have gone to the "Data Load Editor" and modified the entire loading script so that the format of Dates is 100% compatible with my column Date. Before it was in standard Qlik Sense Desktop, and I have changed it to German format (DD.MM.YYYY), as it can be seen here:
I have also updated my .qvf sample file. Now, my Date column I believe is in Date format as it can be seen through this functions (Day(), Year(), Month()):
Don´t know if this can be any helpful to make some progress/targets your suggestions? Thanks, Anibal
-
Mall ONE.qvf 480.0 K
-