Convert date rows into date columns

[valpassos]

Hi folks,

I have a file with this structure (ignore dummy values):

And I simply want to convert the Date rows into various columns (one column for each year-month) because I have the requirement to see the data in a table with this format. I cannot use Pivot Table because pivot table does an aggregation (the Totals rows) that, for this purpose, is not accurate.

I have tried the Generic Load but I'm not getting anything. I have to use a resident and preceding load with it and it keeps saying "Table 'myGenericTable' not found"

Any help?

Thanks!

Lisa

[niclaz79]

Hi,

Can't you use a pivot table but instead of using a sum() calculation use an only() calculation? This way there should be no aggregation.

[OmarBenSalem]

can u show us (create a simple mock table) with the output u desire?

[valpassos]

Hi Niclas,

I don't see any option to define the aggregation in the pivot table. Not at the level of Measures. 

I think it's a default behaviour of pivot tables.

[valpassos]

Hi Omnar,

This would be the output table:

So, it's the opposite of a crosstable.

[JordyWegman]

Hi Lisa,

Maybe do the following, add this line to your script:

Year(Date) & '|' & Month(Date) as YrMth

Make a pivot table in Qlik Sense

Dimensions:

Row

Class

Column

YrMth

Measures:

Count(Class)

With this, you will get the same as in your example.

[valpassos]

Hi Jordy,

What are you doing to the 'Value' column? This is, the numbers I want to populate the table with. You are doing Count(Class).

[JordyWegman]

Hi Lisa,

Then you can do Sum(Value), because I thought you were counting the amount of classes in a certain month.

[valpassos]

Hi Jordy,

No, the values are a pre-calculated value from the file. They are percentages, so instead of sum() I have avg(), but in a pivot table I don't want to see the total avg(). This is the only problem I have with the pivot table. Do you know how can I fix that?

Thanks!

[MK9885]

Can you provide a sample?

You have %'s pre calculated in your data?